package cn.njxzc.dao;


import javax.swing.*;
import java.io.*;
import java.sql.*;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.*;

public class Dao {

	protected static String dbClassName = "com.mysql.jdbc.Driver";// MySQL数据库驱动类的名称
	protected static String dbUrl = "jdbc:mysql://127.0.0.1:3306/db_database28";// 访问MySQL数据库的路径
	protected static String dbUser = "root";// 访问MySQL数据库的用户名
	protected static String dbPwd = "123456";// 访问MySQL数据库的密码
	protected static String dbName = "db_database28";// 访问MySQL数据库中的实例(db_database28)
	protected static String second = null;//
	public static Connection conn = null;// MySQL数据库的连接对象

	static {// 静态初始化Dao类
		try {
			if (conn == null) {
				Class.forName(dbClassName).newInstance();// 实例化MySQL数据库的驱动
				conn = DriverManager.getConnection(dbUrl, dbUser, dbPwd);// 连接MySQL数据库
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "请将MySQL的JDBC驱动包复制到lib文件夹中。");// 捕获异常后，弹出提示框
			System.exit(-1);// 系统停止运行
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private Dao() {
	}

	// 数据库备份
	public static String backup() throws SQLException {
		LinkedList<String> sqls = new LinkedList<String>();// 备份文件中的所有sql
		// 涉及的相关表命数组
		String tables[] = { "tb_gysinfo", "tb_jsr", "tb_khinfo", "tb_kucun",
				"tb_rkth_detail", "tb_rkth_main", "tb_ruku_detail",
				"tb_ruku_main", "tb_sell_detail", "tb_sell_main", "tb_spinfo",
				"tb_userlist", "tb_xsth_detail", "tb_xsth_main" };
		ArrayList<Tables> tableList = new ArrayList<Tables>();// 创建保存所有表对象的集合
		for (int i = 0; i < tables.length; i++) {// 遍历表名称数组
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("desc " + tables[i]);// 查询表结构
			ArrayList<Columns> columns = new ArrayList<Columns>();// 列集合
			while (rs.next()) {
				Columns c = new Columns();// 创建列对象
				c.setName(rs.getString("Field"));// 读取列名
				c.setType(rs.getString("Type"));// 读取列类型
				String isnull = rs.getString("Null");// 读取为空类型
				if ("YES".equals(isnull)) {// 如果列可以为空
					c.setNull(true);// 列可以为空
				}
				String key = rs.getString("Key");// 读取主键类型
				if ("PRI".equals(key)) {// 如果是主键
					c.setKey(true);// 列为主键
					String increment = rs.getString("Extra");// 读取特殊属性
					if ("auto_increment".equals(increment)) {// 表主键是否自增
						c.setIncrement(true);// 主键自增
					}
				}
				columns.add(c);// 列集合添加此列
			}
			Tables table = new Tables(tables[i], columns);// 创建表示此表命和拥有对应列对象的表对象
			tableList.add(table);// 表集合保存此表对象
			rs.close();// 关闭结果集
			stmt.close();// 关闭sql语句接口
		}

		for (int i = 0; i < tableList.size(); i++) {// 遍历表对象集合
			Tables table = tableList.get(i);// 获取表格对象

			String dropsql = "DROP TABLE IF EXISTS " + table.getName() + " ;";// 删除表sql
			sqls.add(dropsql);// 添加删除表sql

			StringBuilder createsql = new StringBuilder();// 创建表sql
			createsql.append("CREATE TABLE " + table.getName() + "( ");// 创建语句句头
			ArrayList<Columns> columns = table.getColumns();// 获取表中所有列对象
			for (int k = 0; k < columns.size(); k++) {// 遍历列集合
				Columns c = columns.get(k);// 获取列对象
				createsql.append(c.getName() + " " + c.getType());// 添加列名和类型声明语句
				if (!c.isNull()) {// 如果列可以为空
					createsql.append(" not null ");// 添加可以为空语句
				}
				if (c.isKey()) {// 如果是主键
					createsql.append(" primary key ");// 添加主键语句
					if (c.isIncrement()) {// 如果是主键自增
						createsql.append(" AUTO_INCREMENT ");// 添加自增语句
					}
				}
				if (k < columns.size() - 1) {// 如果不是最后一列
					createsql.append(",");// 添加逗号
				} else {// 如果是最后一列
					createsql.append(");");// 创建语句结尾
				}
			}
			sqls.add(createsql.toString());// 添加创建表sql

			Statement stmt = conn.createStatement();// 执行sql接口
			ResultSet rs = stmt
					.executeQuery("select * from " + table.getName());
			while (rs.next()) {
				StringBuilder insertsql = new StringBuilder();// 插入值sql
				insertsql.append("INSERT INTO " + table.getName() + " VALUES(");
				for (int j = 0; j < columns.size(); j++) {// 遍历表中所有列
					Columns c = columns.get(j);// 获取列对象
					String type = c.getType();// 获取列字段修饰符
					if (type.startsWith("varchar") || type.startsWith("char")
							|| type.startsWith("datetime")) {// 如果数据类型开头用varchar、char、datetime任意一种修饰
						insertsql.append("'" + rs.getString(c.getName()) + "'");// 获取本列数据，两端加逗号
					} else {
						insertsql.append(rs.getString(c.getName()));// 获取本列数据，两端不加逗号
					}
					if (j < columns.size() - 1) {// 如果不是最后一列
						insertsql.append(",");// 添加逗号
					} else {// 如果是最后一列
						insertsql.append(");");// 添加句尾
					}
				}
				sqls.add(insertsql.toString());// 添加插入数据sql
			}
			rs.close();// 关闭结果集
			stmt.close();// 关闭sql语句接口
		}
		sqls.add("DROP VIEW IF EXISTS v_rukuView;"); // 插入删除视图语句
		// 插入创建爱视图语句
		sqls.add("CREATE VIEW v_rukuView AS SELECT tb_ruku_main.rkID, tb_ruku_detail.spid, tb_spinfo.spname, tb_spinfo.gg, tb_ruku_detail.dj, tb_ruku_detail.sl,tb_ruku_detail.dj * tb_ruku_detail.sl AS je, tb_spinfo.gysname, tb_ruku_main.rkdate, tb_ruku_main.czy, tb_ruku_main.jsr,tb_ruku_main.jsfs FROM tb_ruku_detail INNER JOIN tb_ruku_main ON tb_ruku_detail.rkID = tb_ruku_main.rkID INNER JOIN tb_spinfo ON tb_ruku_detail.spid = tb_spinfo.id;");
		sqls.add("DROP VIEW IF EXISTS v_sellView;");// 插入删除视图语句
		// 插入创建爱视图语句
		sqls.add("CREATE VIEW v_sellView AS SELECT tb_sell_main.sellID, tb_spinfo.spname, tb_sell_detail.spid, tb_spinfo.gg, tb_sell_detail.dj, tb_sell_detail.sl,tb_sell_detail.sl * tb_sell_detail.dj AS je, tb_sell_main.khname, tb_sell_main.xsdate, tb_sell_main.czy, tb_sell_main.jsr,tb_sell_main.jsfs FROM tb_sell_detail INNER JOIN tb_sell_main ON tb_sell_detail.sellID = tb_sell_main.sellID INNER JOIN tb_spinfo ON tb_sell_detail.spid = tb_spinfo.id;");

		java.util.Date date = new java.util.Date();// 通过Date对象获得当前时间
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd_HHmmss");// 设置当前时间的输出格式
		String backupTime = sdf.format(date);// 格式化Date对象
		String filePath = "backup\\" + backupTime + ".sql";// 通过拼接字符串获得备份文件的存放路径

		File sqlFile = new File(filePath);// 创建备份文件对象
		FileOutputStream fos = null;// 文件字节输出流
		OutputStreamWriter osw = null;// 字节流转为字符流
		BufferedWriter rw = null;// 缓冲字符流
		try {
			fos = new FileOutputStream(sqlFile);
			osw = new OutputStreamWriter(fos);
			rw = new BufferedWriter(osw);
			for (String tmp : sqls) {// 遍历所有备份sql
				rw.write(tmp);// 向文件中写入sql
				rw.newLine();// 文件换行
				rw.flush();// 字符流刷新
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			// 倒序依次关闭所有IO流
			if (rw != null) {
				try {
					rw.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			if (osw != null) {
				try {
					osw.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			if (fos != null) {
				try {
					fos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return filePath;
	}

	// 数据库恢复
	public static void restore(String filePath) {
		File sqlFile = new File(filePath);// 创建备份文件对象
		Statement stmt = null;// sql语句直接接口
		FileInputStream fis = null;// 文件输入字节流
		InputStreamReader isr = null;// 字节流转为字符流
		BufferedReader br = null;// 缓存输入字符流
		try {
			fis = new FileInputStream(sqlFile);
			isr = new InputStreamReader(fis);
			br = new BufferedReader(isr);
			String readStr = null;// 缓冲字符串，保存备份文件中一行的内容
			while ((readStr = br.readLine()) != null) {// 逐行读取备份文件中的内容
				if (!"".equals(readStr.trim())) {// 如果读取的内容不为空
					stmt = conn.createStatement();// 创建sql语句直接接口
					int count = stmt.executeUpdate(readStr);// 执行sql语句
					stmt.close();// 关闭接口
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			// 倒序依次关闭所有IO流
			if (br != null) {
				try {
					br.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			if (isr != null) {
				try {
					isr.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			if (fis != null) {
				try {
					fis.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	// 读取所有客户信息
	public static List getKhInfos() {
		List list = findForList("select id,khname from tb_khinfo");
		return list;
	}

	// 读取所有供应商信息
	public static List getGysInfos() {
		List list = findForList("select id,name from tb_gysinfo");
		return list;
	}





	// 执行指定查询
	public static ResultSet query(String QueryStr) {
		ResultSet set = findForResultSet(QueryStr);
		return set;
	}

	// 执行删除
	public static int delete(String sql) {
		return update(sql);
	}




	// 获取所有商品信息
	public static List getSpInfos() {
		List list = findForList("select * from tb_spinfo");
		return list;
	}



	public static ResultSet findForResultSet(String sql) {
		if (conn == null)
			return null;
		long time = System.currentTimeMillis();
		ResultSet rs = null;
		try {
			Statement stmt = null;
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(sql);
			second = ((System.currentTimeMillis() - time) / 1000d) + "";
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rs;
	}

	// 添加数据
	public static boolean insert(String sql) {
		boolean result = false;
		try {
			Statement stmt = conn.createStatement();
			result = stmt.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	// 更新数据
	public static int update(String sql) {
		int result = 0;
		try {
			Statement stmt = conn.createStatement();
			result = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public static List findForList(String sql) {
		List<List> list = new ArrayList<List>();
		ResultSet rs = findForResultSet(sql);
		try {
			ResultSetMetaData metaData = rs.getMetaData();
			int colCount = metaData.getColumnCount();
			while (rs.next()) {
				List<String> row = new ArrayList<String>();
				for (int i = 1; i <= colCount; i++) {
					String str = rs.getString(i);
					if (str != null && !str.isEmpty())
						str = str.trim();
					row.add(str);
				}
				list.add(row);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}


	// 获取销售主表最大ID
	public static String getSellMainMaxId(Date date) {
		return getMainTypeTableMaxId(date, "tb_sell_main", "XS", "sellID");
	}


	// 获取更类主表最大ID
	private static String getMainTypeTableMaxId(Date date, String table,
			String idChar, String idName) {
		String dateStr = date.toString().replace("-", "");
		String id = idChar + dateStr;
		String sql = "select max(" + idName + ") from " + table + " where "
				+ idName + " like '" + id + "%'";
		ResultSet set = query(sql);
		String baseId = null;
		try {
			if (set.next())
				baseId = set.getString(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		baseId = baseId == null ? "000" : baseId.substring(baseId.length() - 3);
		int idNum = Integer.parseInt(baseId) + 1;
		id += String.format("%03d", idNum);
		return id;
	}




	// 添加用户
//	public static int addJsr(TbJsr jsr) {
//		String sql = "insert tb_jsr values('" + jsr.getName() + "','"
//				+ jsr.getSex() + "','" + jsr.getAge() + "','" + jsr.getTel()
//				+ "',1)";
//		System.out.println(sql);
//		return update(sql);
//	}

	public static List getJsrs() {
		List list = findForList("select * from tb_jsr where enable=1");
		return list;
	}

	// 修改用户方法
	public static int modifyPassword(String oldPass, String pass) {
		return update("update tb_userlist set pass='" + pass + "' where pass='"
				+ oldPass + "'");
	}


	// 验证登录
	public static boolean checkLogin(String userStr, String passStr)
			throws SQLException {
		ResultSet rs = findForResultSet("select * from tb_userlist where name='"
				+ userStr + "' and pass='" + passStr + "'");
		if (rs == null)
			return false;
		return rs.next();
	}

}